package com.fun.demo.fun.kayang;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.UUID;

public class Poi {


    public static void main(String[] args) throws IOException, InterruptedException {
        //readXlsx();
            writeXlsx(1_000_000, 300);
    }

    //read
    public static void readXlsx() throws IOException {
        String path = "D:/小数decimal_20201116155722.xlsx";
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(new File(path)));
        int sheetSize = xssfWorkbook.getNumberOfSheets();

        for (int i = 0; i < sheetSize; i++) {
            Sheet sheet = xssfWorkbook.getSheetAt(i);
            System.out.println( sheet.getSheetName() + " : " );
            int rows =sheet.getPhysicalNumberOfRows();
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            String [] [] result = new String[cells][rows];

            for (int j = 0; j < rows; j++) {
                Row row = sheet.getRow(j);
                for (int k = 0; k < cells; k++) {
                    Cell cell = row.getCell(k);
                    Object value = null;
                    if (cell == null){
                        result[k][j] = "CELL_NULL";
                        continue;
                    }
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;

                        case Cell.CELL_TYPE_BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;

                        case Cell.CELL_TYPE_NUMERIC:
                            value = cell.getNumericCellValue();
                            break;

                        case Cell.CELL_TYPE_FORMULA:
                            value = cell.getNumericCellValue();
                            break;

                        case Cell.CELL_TYPE_BLANK:
                            value = "";
                            break;

                        case Cell.CELL_TYPE_ERROR:
                            value = cell.getErrorCellValue();
                            break;

                        default:
                            value = "";
                    }
                    result[k][j] = value.toString();
                }
            }

            for (int l = 0; l < rows; l++) {
                System.out.println();
                for (int m = 0; m < cells; m++) {
                    String s = result[m][l];
                    System.out.print(s + " , " );
                }

            }
            System.out.println("\n\n\n");

        }



    }


    //write
    public static void writeXlsx(int rows, int cols) throws InterruptedException {

        SXSSFWorkbook workbook = new SXSSFWorkbook(10_000);
        workbook.setCompressTempFiles(true);
        CellStyle style = workbook.createCellStyle();

        CellStyle dstyle = workbook.createCellStyle();
        dstyle.setWrapText(true);
        dstyle.cloneStyleFrom(style);

        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.cloneStyleFrom(style);


        Sheet sheet = workbook.createSheet("Poi测试");

        CellStyle cs = workbook.createCellStyle();
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        for (int i = 0; i < rows; i++) {
            Row row = sheet.createRow(i);
            if(i % 1000 ==0) {
                Thread.sleep(1000);
                System.out.println("sleep 1S i:" + i);
            }
            for (int j = 0; j < cols; j++) {
//                short s = 4;
//                cs.setDataFormat(s);
                Cell cell = row.createCell(j);
                cell.setCellValue(100012 + i * j);
                cell.setCellStyle(cs);
                cs.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
            }
        }


        String filePath = "";
        FileOutputStream outputStream = null;
        try {

            filePath = "D:/" + UUID.randomUUID() + ".xlsx";
            outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);

            System.out.println("Build Success.");

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                workbook.dispose();
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


}
